﻿using Mysoft.QuickCode.NPOI.HSSF.UserModel;
using Mysoft.QuickCode.NPOI.SS.UserModel;
using Mysoft.QuickCode.NPOI.SS.Util;
using Mysoft.QuickCode.Office.Excel.Data;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace Mysoft.QuickCode.Office.Excel
{
    public class ExcelCreator
    {
        /// <summary>
        /// Excel单元格左边与数字转换字典
        /// </summary>
        static Dictionary<int, string> AZDict = new Dictionary<int, string>(){
                {65, "A"},
                {66, "B"},
                {67, "C"},
                {68, "D"},
                {69, "E"},
                {70, "F"},
                {71, "G"},
                {72, "H"},
                {73, "I"},
                {74, "J"},
                {75, "K"},
                {76, "L"},
                {77, "M"},
                {78, "N"},
                {79, "O"},
                {80, "P"},
                {81, "Q"},
                {82, "R"},
                {83, "S"},
                {84, "T"},
                {85, "U"},
                {86, "V"},
                {87, "W"},
                {88, "X"},
                {89, "Y"},
                {90, "Z"}
            };

        /// <summary>
        /// 导出到Excel
        /// </summary>
        /// <param name="rows">要到处的标准矩阵数据</param>
        /// <param name="fileName">导出保存路径</param>
        /// <param name="settings">导出的Excel全局配置信息</param>
        /// <param name="delegateCellSettings">单元格的高级设置信息委托，用于高级导出功能使用，如果没有则设置为null即可。</param>
        /// <param name="sheetName">创建的Excel中的Sheet名称</param>
        /// <param name="createMode">创建模式</param>
        public static void ExportToExcel(List<List<ExcelCell>> rows, string fileName, ExportExcelSetting settings,
            Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>>/*（值，行，列信息，当前行索引）*/ delegateCellSettings = null,
            string sheetName = null,
            CreateMode createMode = CreateMode.Overwrite)
        {
            //渲染单元格位置
            NumberToAZRender(rows);

            IWorkbook hssfworkbook = null;


            if (sheetName != null && createMode != CreateMode.Overwrite)
            {
                if (File.Exists(fileName) == false)
                {
                    hssfworkbook = new HSSFWorkbook();
                }
                else
                {
                    //读取打开现有excel
                    FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
                    hssfworkbook = new HSSFWorkbook(fileStream);
                }
            }
            else
            {
                hssfworkbook = new HSSFWorkbook();
            }

            NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(sheetName ?? ("默认" + (hssfworkbook.NumberOfSheets + 1).ToString()));
            hssfSheet.DefaultColumnWidth = 20;  //表格默认列宽
            hssfSheet.DefaultRowHeight = 20 * 20;   //表格默认行高

            //标题样式  
            NPOI.SS.UserModel.ICellStyle headerStyle = hssfworkbook.CreateCellStyle();
            NPOI.SS.UserModel.IFont headerFont = hssfworkbook.CreateFont();
            headerStyle.Alignment = HorizontalAlignment.Center;
            headerStyle.VerticalAlignment = VerticalAlignment.Center;
            headerStyle.BorderBottom = BorderStyle.Thin;
            headerStyle.BorderBottom = BorderStyle.Thin;
            headerStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            headerStyle.BorderLeft = BorderStyle.Thin;
            headerStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            headerStyle.BorderRight = BorderStyle.Thin;
            headerStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            headerStyle.BorderTop = BorderStyle.Thin;
            headerStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
            headerFont.Boldweight = (short)FontBoldWeight.Bold;
            headerStyle.SetFont(headerFont);

            List<MyCellStyleSetting> cssList = new List<MyCellStyleSetting>();

            //表格列数
            int columnsCount = (rows.Count > 0) ? rows[0].Count : 0;

            #region 表数据填充（包含表头）

            for (int k = 0; k < rows.Count; k++)
            {
                //循环行
                List<ExcelCell> dr = rows[k];
                NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k);
                if (k < settings.HeaderRowCount)
                {
                    //表头部分行高
                    row.Height = 22 * 20;
                }
                for (int i = 0; i < columnsCount; i++)
                {
                    MyCellStyleSetting css = null;

                    //默认单元格配置
                    if (k >= settings.HeaderRowCount && delegateCellSettings != null && delegateCellSettings.ContainsKey(dr[i].Field))
                    {
                        dr[i].Settings = delegateCellSettings[dr[i].Field](dr[i].Text, dr, dr[i], k);
                    }

                    //数据部分行高
                    row.Height = 18 * 20;

                    //循环列
                    ICell cell = row.CreateCell(i);
                    if (k < settings.HeaderRowCount)
                    {
                        //表头采用纯文本存储模式
                        cell.SetCellType(CellType.String);
                        cell.SetCellValue(dr[i].Text);
                    }
                    else
                    {
                        switch (dr[i].DataType)
                        {
                            case "number":
                                {
                                    //能转换为数字，则设置单元格格式为数字格式
                                    string text = Regex.Replace((string.IsNullOrEmpty(dr[i].Text) ? "" : dr[i].Text), ",", "");
                                    double d = 0;
                                    if (Regex.IsMatch(text, "^\\-?\\d+(\\.\\d+)?%$"))
                                    {
                                        //百分比转小数形式
                                        d = double.Parse(Regex.Match(text, "^\\-?\\d+(\\.\\d+)?").Value) / 100.00;
                                        cell.SetCellValue(d);
                                    }
                                    else if (Regex.IsMatch(text, "^\\-?\\d+(\\.\\d+)?$"))
                                    {
                                        //匹配数字值
                                        d = double.Parse(Regex.Match(text, "^\\-?\\d+(\\.\\d+)?").Value);
                                        cell.SetCellValue(d);
                                    }
                                    else
                                    {
                                        //非数字，默认值
                                        cell.SetCellValue(dr[i].Text);
                                    }
                                    break;
                                }
                            default:
                                {
                                    //为兼容以前的版本没有显示指定数据类型，此处做了兼容性自动识别数据类型处理。
                                    double d = 0;
                                    string text = Regex.Replace((string.IsNullOrEmpty(dr[i].Text) ? "" : dr[i].Text), ",", "");
                                    if (dr[i].CellType == CellType.String)
                                    {
                                        cell.SetCellType(dr[i].CellType);
                                        cell.SetCellValue(dr[i].Text);
                                    }
                                    else if (text == "0" || ((text.StartsWith("0") && text.IndexOf(".") == -1) == false && double.TryParse(text, out d)))
                                    {
                                        //能转换为数字，则设置单元格格式为数字格式
                                        cell.SetCellType(CellType.Numeric);
                                        cell.SetCellValue(d);
                                    }
                                    else
                                    {
                                        cell.SetCellValue(dr[i].Text);
                                    }
                                    break;
                                }
                        }
                    }

                    if (k < settings.HeaderRowCount)
                    {
                        //表头部分做表头样式
                        cell.CellStyle = headerStyle;

                        //列宽度
                        int width = (int)Math.Round((double)dr[i].Width / 10.0);  //像素宽度转为excel表格的宽度
                        if (dr[i].Width < 0)
                        {
                            hssfSheet.SetColumnWidth(i, 0); //隐藏列
                        }
                        else
                        {
                            hssfSheet.SetColumnWidth(i, ((width == 0 ? 15 : width) + 8) * 256);
                        }
                    }
                    else
                    {
                        css = new MyCellStyleSetting()
                        {
                            IsLocked = dr[i].Settings.Locked    //默认值
                        };
                        css.DataFormat = dr[i].DataFormat;
                        css.BackgroundColor = dr[i].BackgroundColor;
                        css.FontColor = dr[i].FontColor;

                        //对齐方式
                        //cellStyle.Alignment
                        css.Alignment = new Func<string, HorizontalAlignment>((align) =>
                        {
                            align = align ?? string.Empty;
                            switch (align.ToLower())
                            {
                                case "center":
                                    {
                                        return HorizontalAlignment.Center;
                                    }
                                case "right":
                                    {
                                        return HorizontalAlignment.Right;
                                    }
                                default:
                                    return HorizontalAlignment.Left;
                            }
                        }).Invoke(dr[i].Align);
                        //cell.CellStyle = cellStyle;
                    }

                    //高级Excel导出
                    if (settings.ECellable)
                    {
                        //单元格公式
                        if (!string.IsNullOrEmpty(dr[i].Settings.Formula))
                        {
                            cell.SetCellFormula(dr[i].Settings.Formula);
                        }

                        //单元格保护样式
                        //NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
                        //cellStyle.CloneStyleFrom(cell.CellStyle);
                        //ICellStyle cellStyle = cell.CellStyle;
                        //cellStyle.IsLocked = dr[i].Settings.Locked;
                        if (css != null)
                        {
                            css.IsLocked = dr[i].Settings.Locked;
                        }
                        //if (dr[i].Settings.Locked)
                        //{
                        //    //保护单元格的样式为灰色背景
                        //    cellStyle.FillPattern = FillPattern.SolidForeground;
                        //    cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Gold.Index;
                        //}
                        //cell.CellStyle = cellStyle;
                    }

                    //创建样式
                    Predicate<MyCellStyleSetting> match = new Predicate<MyCellStyleSetting>(m => m.Alignment == css.Alignment
                            && m.IsLocked == css.IsLocked
                            && m.DataFormat == css.DataFormat
                            && m.BackgroundColor == css.BackgroundColor
                            && m.FontColor == css.FontColor); //重复样式搜索条件
                    Func<MyCellStyleSetting, bool> funcMatch = new Func<MyCellStyleSetting, bool>(m => m.Alignment == css.Alignment
                            && m.IsLocked == css.IsLocked
                            && m.DataFormat == css.DataFormat
                            && m.BackgroundColor == css.BackgroundColor
                            && m.FontColor == css.FontColor); //匹配函数
                    if (css != null
                        && cssList.Exists(match))
                    {
                        //重复css不再创建新的CellStyle
                        cell.CellStyle = cssList.First(funcMatch).CellStyle;
                    }
                    else if (css != null
                        && cssList.Exists(match) == false)
                    {
                        //不重复的样式，则新创建样式
                        NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();

                        //边框
                        cellStyle.BorderBottom = BorderStyle.Thin;
                        cellStyle.BorderBottom = BorderStyle.Thin;
                        cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                        cellStyle.BorderLeft = BorderStyle.Thin;
                        cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                        cellStyle.BorderRight = BorderStyle.Thin;
                        cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                        cellStyle.BorderTop = BorderStyle.Thin;
                        cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;

                        cellStyle.Alignment = css.Alignment;
                        cellStyle.IsLocked = css.IsLocked;
                        if (string.IsNullOrEmpty(css.DataFormat) == false)
                        {
                            //"#,##0.00"
                            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(css.DataFormat);
                            if (cellStyle.DataFormat == -1)
                            {
                                cellStyle.DataFormat = hssfworkbook.CreateDataFormat().GetFormat(css.DataFormat);
                            }
                        }
                        if (cellStyle.IsLocked && settings.ECellable)
                        {
                            //锁定单元格的颜色
                            cellStyle.FillPattern = FillPattern.SolidForeground;
                            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Gold.Index;
                        }
                        if (css.BackgroundColor != NPOI.HSSF.Util.HSSFColor.COLOR_NORMAL)
                        {
                            //白色
                            //设置了背景色
                            cellStyle.FillPattern = FillPattern.SolidForeground;
                            cellStyle.FillForegroundColor = css.BackgroundColor;
                        }
                        if (css.FontColor != NPOI.HSSF.Util.HSSFColor.Black.Index)
                        {
                            //黑色
                            //设置了背景色
                            IFont font = hssfworkbook.CreateFont();//创建字体样式  
                            font.Color = css.FontColor;//设置字体颜色  
                            cellStyle.SetFont(font);//设置单元格样式中的字体样式  
                        }
                        css.CellStyle = cellStyle;
                        cell.CellStyle = css.CellStyle;
                        cssList.Add(css);
                    }
                }
            }

            //高级Excel导出
            if (settings.ECellable)
            {
                hssfSheet.ForceFormulaRecalculation = true;
                hssfSheet.ProtectSheet("3.1415926");    //保护密码
            }

            #endregion

            #region 后期处理：合并表头单元格、冻结行列

            //处理冻结行列
            int colSplit = 0,
                rowSplit = (settings.HeaderRowCount > 0) ? settings.HeaderRowCount : 0;  //冻结行列位置索引
            if (rows.Count > 0)
            {
                //获取列单元格索引
                ExcelCell c = rows[settings.HeaderRowCount - 1].LastOrDefault(m => m.ColumnFreeze); //表头的最后一行一定为完整列设置行
                colSplit = rows[settings.HeaderRowCount - 1].LastIndexOf(c);
                colSplit = colSplit >= 0 ? colSplit + 1 : 0;
            }
            hssfSheet.CreateFreezePane(colSplit, rowSplit); //冻结


            //横向待合并列
            for (int rowIndex = 0; rowIndex < settings.HeaderRowCount; rowIndex++)
            {
                //横向合并
                List<int> mgList = new List<int>();
                for (int cellIndex = 0; cellIndex < columnsCount; cellIndex++)
                {
                    if (mgList.Count == 0)
                    {
                        mgList.Add(cellIndex);
                    }
                    else
                    {
                        //当前单元格与前面单元格内容一样，则加入待合并列表
                        if (hssfSheet.GetRow(rowIndex).GetCell(cellIndex).ToString().Trim() == hssfSheet.GetRow(rowIndex).GetCell(mgList.Last()).ToString().Trim()
                            && rows[rowIndex][cellIndex].ColumnGroup == rows[rowIndex][mgList.Last()].ColumnGroup)
                        {
                            mgList.Add(cellIndex);
                        }
                        else
                        {
                            //当前单元格与前面单元格内容不一样，则先合并前面单元格内容
                            if (mgList.Count > 1)
                            {
                                CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, mgList.First(), mgList.Last());
                                hssfSheet.AddMergedRegion(cellRangeAddress);
                            }

                            //重新初始化准备下一组合并
                            mgList = new List<int>();
                            mgList.Add(cellIndex);
                        }
                    }
                }
                //循环结束后：当前单元格与前面单元格内容不一样，则先合并前面单元格内容
                if (mgList.Count > 1)
                {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, mgList.First(), mgList.Last());
                    hssfSheet.AddMergedRegion(cellRangeAddress);
                }
            }

            //纵向合并
            for (int cellIndex = 0; cellIndex < columnsCount; cellIndex++)
            {
                //横向合并
                List<int> mgList = new List<int>();
                for (int rowIndex = 0; rowIndex < settings.HeaderRowCount; rowIndex++)
                {
                    if (mgList.Count == 0)
                    {
                        mgList.Add(rowIndex);
                    }
                    else
                    {
                        //当前单元格与前面单元格内容一样，则加入待合并列表
                        if (hssfSheet.GetRow(rowIndex).GetCell(cellIndex).ToString().Trim() == hssfSheet.GetRow(mgList.Last()).GetCell(cellIndex).ToString().Trim()
                            && rows[rowIndex][cellIndex].ColumnGroup == rows[mgList.Last()][cellIndex].ColumnGroup)
                        {
                            mgList.Add(rowIndex);
                        }
                        else
                        {
                            //当前单元格与前面单元格内容不一样，则先合并前面单元格内容
                            if (mgList.Count > 1)
                            {
                                CellRangeAddress cellRangeAddress = new CellRangeAddress(mgList.First(), mgList.Last(), cellIndex, cellIndex);
                                hssfSheet.AddMergedRegion(cellRangeAddress);
                            }

                            //重新初始化准备下一组合并
                            mgList = new List<int>();
                            mgList.Add(rowIndex);
                        }
                    }
                }
                //循环结束后：当前单元格与前面单元格内容不一样，则先合并前面单元格内容
                if (mgList.Count > 1)
                {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(mgList.First(), mgList.Last(), cellIndex, cellIndex);
                    hssfSheet.AddMergedRegion(cellRangeAddress);
                }
            }

            #endregion

            ////触发所有计算公式刷新
            HSSFFormulaEvaluator formulaEval = new HSSFFormulaEvaluator(hssfworkbook);
            formulaEval.EvaluateAll();

            using (FileStream file = new FileStream(fileName, FileMode.OpenOrCreate))
            {
                hssfworkbook.Write(file);
            }
        }


        /// <summary>
        /// 为矩阵渲染单元格在Excel中的位置标记
        /// </summary>
        /// <param name="lstTable">矩阵行</param>
        /// <returns></returns>
        static void NumberToAZRender(List<List<ExcelCell>> lstTable)
        {
            int rowNumber = 1;  //Excel中的单元格行数从1开始计数
            foreach (List<ExcelCell> row in lstTable)
            {
                int colNumber = 1;   //列号从1开始计数
                foreach (ExcelCell cell in row)
                {
                    int dividend = colNumber;
                    string columnName = "";
                    while (dividend > 0)
                    {
                        int modulo = (dividend - 1) % 26;
                        columnName = AZDict[65 + modulo] + columnName;
                        dividend = (int)((dividend - modulo) / 26);
                    }
                    cell.XY = columnName + rowNumber;
                    ++colNumber;
                }
                ++rowNumber;
            }
        }
    }

    public static class ExcelCellTableExtend
    {

        /// <summary>
        /// 按条件过滤搜索表格中对应的单元格信息
        /// </summary>
        /// <param name="lstTable">矩阵结构的Excel表格数据</param>
        /// <param name="filter">过滤条件。优先按索引RowIndex搜索，否则按FieldFilter条件搜索。</param>
        /// <returns></returns>
        public static ExcelCell GetExcelCell(this List<List<ExcelCell>> lstTable, ExcelCellFilter filter)
        {
            //获取高级Excel导出所需的单元格
            ExcelCell ecell = null;
            if (filter.RowIndex >= 0)
            {
                ecell = lstTable[filter.RowIndex].FirstOrDefault(m => m.Field == filter.Field);
            }
            else
            {
                int rIndex = lstTable.FindIndex(r => r.Exists(c => c.Field == filter.FilterField && c.TrimText == filter.FilterValue));
                if (rIndex >= 0)
                {
                    ecell = lstTable[rIndex].FirstOrDefault(m => m.Field == filter.Field);
                }
            }
            return ecell;
        }

        /// <summary>
        /// 获取单元格的位置
        /// </summary>
        /// <param name="lstTable">表格数据</param>
        /// <param name="filter">过滤条件</param>
        /// <param name="noCellValue">获取不到或不存在的情况下返回字符串代替公式中的计算值，默认为0</param>
        /// <returns></returns>
        public static string GetExcelCellXY(this List<List<ExcelCell>> lstTable, ExcelCellFilter filter, string noCellValue = "0")
        {
            ExcelCell ecell = GetExcelCell(lstTable, filter);
            return (ecell ?? new ExcelCell() { XY = noCellValue }).XY;
        }

        /// <summary>
        /// 获取行中，在当前记录行中在属于指定列分组中的字段。
        /// </summary>
        /// <param name="lstRow">当前数据行</param>
        /// <param name="group">列分组标识</param>
        /// <returns></returns>
        public static string[] GetFieldNamesByGroup(this List<ExcelCell> lstRow, string group)
        {
            return lstRow.Where(m => m.ColumnGroup == group).Select(m => m.Field).ToArray();
        }

        /// <summary>
        /// 在当前行中获取指定单元格
        /// </summary>
        /// <param name="lstRow">数据行</param>
        /// <param name="fieldName">字段名称</param>
        /// <returns></returns>
        public static ExcelCell GetExcelCellByField(this List<ExcelCell> lstRow, string fieldName)
        {
            return lstRow.FirstOrDefault(m => m.Field == fieldName);
        }

        /// <summary>
        /// 获取指定行的指定字段单元格
        /// </summary>
        /// <param name="lstTable">表数据</param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="fieldName">字段名称</param>
        /// <returns></returns>
        public static ExcelCell GetExcelCellByField(this List<List<ExcelCell>> lstTable, int rowIndex, string fieldName)
        {
            return lstTable[rowIndex].FirstOrDefault(m => m.Field == fieldName);
        }


        /// <summary>
        /// 获取指定行的指定字段单元格
        /// </summary>
        /// <param name="lstTable">表数据</param>
        /// <param name="rowIndex">行索引</param>
        /// <param name="fieldName">字段名称</param>
        /// <returns></returns>
        public static List<List<ExcelCell>> ToExcelCellList(this DataTable table)
        {
            List<List<ExcelCell>> list = new List<List<ExcelCell>>();

            //列头
            list.Add(new List<ExcelCell>());
            foreach (DataColumn col in table.Columns)
            {
                list.Last().Add(new ExcelCell()
                {
                    Field = col.ColumnName,
                    Text = col.ColumnName
                });
            }

            //数据域
            foreach (DataRow row in table.Rows)
            {
                list.Add(new List<ExcelCell>());
                foreach (DataColumn col in table.Columns)
                {
                    list.Last().Add(new ExcelCell()
                    {
                        Field = col.ColumnName,
                        Text = row[col.ColumnName].ToString()
                    });
                }
            }

            return list;
        }

    }

    public enum CreateMode
    {
        /// <summary>
        /// 重新创建写入
        /// </summary>
        Overwrite = 1,
        /// <summary>
        /// 在现有Excel中追加Sheet模式
        /// </summary>
        AppendSheet = 2
    }
}
